﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.util.*;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class ContractJobCvRecomDaoImpl extends BaseHibernateDao<ContractJobCvRecom, java.lang.Integer> implements ContractJobCvRecomDao<ContractJobCvRecom, java.lang.Integer> {

	public Class getEntityClass() {
		return ContractJobCvRecom.class;
	}

	public List findJobRecomCv(ContractJobCvRecomQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		//
		sql.append(" select * from ( ");
		sql.append(" select  a.id ,a.is_email isEmail,b.cv_name cvName from  contract_job_cv_recom a,cv b where a.cv_id=b.id and a.job_id=? and b.is_delete=0 ");
		sql.append(" ) t where 1=1 ");
		values.add(query.getJobId());

		if (isNotEmpty(query.getIsEmail())) {
			sql.append(" and t.isEmail = ? ");
			values.add(query.getIsEmail());
		}

		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

	public Page findJobRecomCvPage(ContractJobCvRecomQuery query) {
		// 人选姓名，职位，客户职位联系人，推荐日期，项目负责人，信息录入人，手机，电邮，客户公司

		// 客户公司、职位名、候选人名、现任公司、现职位、手机、电邮、推荐时间、职位负责人、人选进展
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();

		/*
		 * sql.append(" select * from ( "); //sql.append(
		 * " select a.id,a.cust_id custId,a.id recomId,a.job_id jobId,h.jobManager,a.status cvStatus,e.project_id projectId,a.job_no jobNo,a.cv_id cvId,b.cv_name cvName,b.company cvCompany,b.title cvTitle,a.name jobName,d.cv_name jobContacter,e.account owner,b1.name custName,a.creator,a.create_dt recomDt,a.modified_dt modifiedDt,b.mobile,b.tel phone,b.email,k.contractId  from (select distinct a1.id,a1.creator,a1.create_dt,a1.status,a1.modified_dt,a1.cv_id,a1.job_id,a2.job_no,a2.name,a2.hr_contacter,a2.ar_contacter,a2.cust_id from contract_job_cv_recom a1,contract_job a2 where  a1.job_id=a2.id) a "
		 * ); sql.append(
		 * " select a.id,a.cust_id custId,a.id recomId,a.job_id jobId,a.job_manager jobManager,a.status cvStatus,l.project_id projectId,a.job_no jobNo,a.cv_id cvId,b.cv_name cvName,b.company cvCompany,b.title cvTitle,a.name jobName,'' jobContacter,b1.name custName,a.creator,a.create_dt recomDt,a.modified_dt modifiedDt,b.mobile,b.tel phone,b.email,k.contractId  from (select distinct a1.id,a1.creator,a1.create_dt,a1.status,a1.modified_dt,a1.cv_id,a1.job_id,a2.job_no,a2.name,a2.hr_contacter,a2.ar_contacter,a2.cust_id,a2.job_manager  from contract_job_cv_recom a1,contract_job a2 where  a1.job_id=a2.id) a "
		 * ); sql.append(" left join  cv b on a.cv_id=b.id ");
		 * 
		 * sql.append(
		 * " left join  (select t2.id,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on a.cust_id=b1.id "
		 * ); sql.append(
		 * " left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a.cust_id=k.id "
		 * ); //sql.append(
		 * " left join (select a.company_id,max(b.id) contractId from customer a,customer_contract b,company c where a.id=b.cust_id and a.company_id=c.id group by a.company_id) l on a.company_id=l.company_id "
		 * ); sql.append(
		 * " left join (select aa.job_id,max(aa.project_id) project_id from contract_job_search_project aa group by aa.job_id) l on l.job_id=a.job_id "
		 * ); sql.append(" ) comcv where 1=1 ");
		 */

		sql.append(" select * from ( ");
		sql.append(" select a.id,a2.cust_id custId,a.id recomId,a.job_id jobId,a.is_email isEmail,a2.job_manager jobManager,a.status cvStatus,a2.job_no jobNo,a.cv_id cvId,b.cv_name cvName,b.company cvCompany,b.title cvTitle,a2.name jobName,'' jobContacter,a.creator,a.create_dt createDt,a.modified_dt modifiedDt,a.recom_dt recomDt,a11.creator recomCreator,b.mobile,b.tel phone,b.email,k.contractId    ");
		// sql.append(" oa.attachment,oa.attachment2  ");
		sql.append(" from contract_job_cv_recom a ");
		sql.append(" inner join  contract_job a2 on  a.job_id=a2.id ");
		sql.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		//b1.abbr_name custName,b1.name chName,b1.full_name enName
		//sql.append(" left join  (select t2.id,t1.abbr_name,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on a2.cust_id=b1.id ");		
		sql.append(" left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a2.cust_id=k.id ");
		// sql.append(" left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a2.cust_id=k.id ");
		// 推荐记录 附件
		// sql.append(" left join  oa_email_send  oa  on a.id=oa.call_id  oder by id ");
		//sql.append(" left join (select a10.cv_id,a10.job_id,a10.creator from contract_job_cv_jd a10 group by a10.cv_id order by a10.create_dt desc  ) a11 on a.cv_id=a11.cv_id and a.job_id = a11.job_id ");
		sql.append(" left join (select a10.cv_id,a10.job_id,a10.creator from contract_job_cv_jd a10 ");
		if (isNotEmpty(query.getJobId())) {
			sql.append(" where a10.job_id=? ");
			values.add(query.getJobId());
		}
		if (isNotEmpty(query.getCvId())&&isNotEmpty(query.getJobId())) {
			sql.append(" and a10.cv_id=? ");
			values.add(query.getCvId());
		}else if (isNotEmpty(query.getCvId())) {
			sql.append(" where a10.cv_id=? ");
			values.add(query.getCvId());
		}
		sql.append(" group by a10.cv_id order by a10.create_dt desc  ) a11 on a.cv_id=a11.cv_id and a.job_id = a11.job_id ");
		sql.append(" ) comcv where 1=1 ");

		if (isNotEmpty(query.getCvId())) {
			sql.append(" and comcv.cvId=? ");
			values.add(query.getCvId());
		}

		if (isNotEmpty(query.getJobId())) {
			sql.append(" and comcv.jobId=? ");
			values.add(query.getJobId());
		}
		if (isNotEmpty(query.getJobName())) {
			sql.append(" and comcv.jobName like ? ");
			values.add("%" + query.getJobName() + "%");
		}
		if (isNotEmpty(query.getCustName())) {
			//sql.append(" and (comcv.custName like ? or chName like ? or enName like ? )");
			
			sql.append(" and exists (select 1 from company t1,customer t2 where t1.id=t2.company_id and comcv.custId=t2.id and (t1.abbr_name like ? or t1.name like ? or t1.full_name like ?) )");
			
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");

		}
		if (isNotEmpty(query.getCvName())) {
			sql.append(" and comcv.cvName like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getJobNo())) {
			sql.append(" and comcv.jobNo like ? ");
			values.add("%" + query.getJobNo() + "%");
		}
		if (isNotEmpty(query.getJobManager())) {
			sql.append(" and comcv.jobManager = ? ");
			values.add(query.getJobManager());
		}
		// 是否发送推荐邮件
		if (isNotEmpty(query.getIsEmail())) {
			sql.append(" and comcv.isEmail = ? ");
			values.add(query.getIsEmail());
		}
		if (isNotEmpty(query.getRecomCreator())) {
			sql.append(" and comcv.recomCreator = ? ");
			values.add(query.getRecomCreator());
		}
		if (isNotEmpty(query.getRecomDtBegin())) {
			sql.append(" and comcv.recomDt >=? ");
			values.add(query.getRecomDtBegin());
		}
		if (isNotEmpty(query.getRecomDtEnd())) {
			sql.append(" and comcv.recomDt < ? ");
			values.add(DateHelper.add(query.getRecomDtEnd(), DateHelper.DAY, 1));
		}
		// 增加权限控制
		System.out.println(sql.toString());
		if (query.isAuth()) {
			if (query.getOnlineUser() != null) {
				String authsql = AuthSqlUtil.authJobRecomGroup(query.getOnlineUser().getUsername(), "comcv.recomCreator");
				if (!StringHelper.isEmpty(authsql)){
					sql.append(authsql);
				}
			}
		}

		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by comcv.").append(query.getSortColumns());
		}
		sql.append(" order by comcv.recomDt desc ");
		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findPage(ContractJobCvRecomQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { ContractJobCvRecomQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuilder sql2 = new StringBuilder("select t from ContractJobCvRecom t where 1=1 ");
		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = :id ");
		}
		if (isNotEmpty(query.getJobId())) {
			sql2.append(" and  t.jobId = :jobId ");
		}
		if (isNotEmpty(query.getCvId())) {
			sql2.append(" and  t.cvId = :cvId ");
		}
		if (isNotEmpty(query.getRecomResult())) {
			sql2.append(" and  t.recomResult like :recomResult ");
			query.setRecomResult("%" + query.getRecomResult() + "%");

		}
		if (isNotEmpty(query.getRecomRisk())) {
			sql2.append(" and  t.recomRisk like :recomRisk ");
			query.setRecomRisk("%" + query.getRecomRisk() + "%");

		}
		if (isNotEmpty(query.getDocSize())) {
			sql2.append(" and  t.docSize like :docSize ");
			query.setDocSize("%" + query.getDocSize() + "%");

		}
		if (isNotEmpty(query.getExportTemplate())) {
			sql2.append(" and  t.exportTemplate = :exportTemplate ");
		}
		if (isNotEmpty(query.getExportDoc())) {
			sql2.append(" and  t.exportDoc like :exportDoc ");
			query.setExportDoc("%" + query.getExportDoc() + "%");

		}
		if (isNotEmpty(query.getUploadDoc())) {
			sql2.append(" and  t.uploadDoc like :uploadDoc ");
			query.setUploadDoc("%" + query.getUploadDoc() + "%");

		}
		if (isNotEmpty(query.getIsSmsnote())) {
			sql2.append(" and  t.isSmsnote like :isSmsnote ");
			query.setIsSmsnote("%" + query.getIsSmsnote() + "%");

		}
		if (isNotEmpty(query.getStatus())) {
			sql2.append(" and  t.status = :status ");
		}
		if (isNotEmpty(query.getCreator())) {
			sql2.append(" and  t.creator like :creator ");
			query.setCreator("%" + query.getCreator() + "%");

		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql2.append(" and  t.createDt >= :createDtBegin ");
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql2.append(" and  t.createDt <= :createDtEnd ");
		}
		if (isNotEmpty(query.getModifier())) {
			sql2.append(" and  t.modifier like :modifier ");
			query.setModifier("%" + query.getModifier() + "%");

		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql2.append(" order by :sortColumns ");
		}

		return pageQuery(sql2.toString(), query);
	}

	public List getContractJobCvRecomById(Integer id) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		//
		sql.append(" select * from ( ");
		sql.append(" select  a.id,a.job_id jobId,a.cv_id cvId,a.recom_result recomResult,a.recom_risk recomRisk,a.doc_size docSize,a.export_template exportTemplate,a.export_doc exportDoc,a.upload_doc uploadDoc,a.upload_doc_en uploadDocEn,a.is_smsnote isSmsnote,a.status,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,a.summary,b.cv_name cvName,c.name jobName from  contract_job_cv_recom a,cv b,contract_job c where a.cv_id=b.id and a.job_id=c.id and a.id=? ");
		sql.append(" ) t ");
		values.add(id);

		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
	}

}
